package comparedatabase;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.date.LocalDateTimeUtil;
import cn.hutool.core.util.StrUtil;
import file.FileUtil;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.Comparator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.stream.Collectors;

public class CompareDatabase {

    private static String[] ignoreTables = {
            "gm_service_flyway_schema_history",
            "pci_log_store_ext_sign",
            "pdman_db_version",
            "sys_user_sign",
            "user_role_sign",
            "viid_dispatched_person_sign",
            "schema_history_table",
            "schema_history_table_work",
            "schema_history_table_job",
            "schema_history_table_cipos_assembly",
            "schema_history_table_cipos_cms",
            "schema_history_table_cipos_edu",
            "schema_history_table_portal",
            "schema_history_table_closure",
            "schema_history_table_job_admin"
    };

    private static String outputBaseDir = "D:\\临时\\";
    private static String tableHandleDDLFileName = "tableHandle.sql";// 输出表处理SQL
    private static String columnHandleDDLFileName = "columnHandle.sql";// 输出列处理SQL
    private static String indexHandleDDLFileName = "indexHandle.sql";// 输出索引处理SQL
    private static String viewHandleDDLFileName = "viewHandle.sql";// 输出视图处理SQL

    private static String outputDir;// 输出路径
    private static DsConfig newDbInfo;// 新版本数据库信息
    private static DsConfig oldDbInfo;// 旧版本数据库信息


    public static void main(String[] args) {
        String db="cipos-work";
        String newDsSchema = db+"-c";// 测试环境数据库（视为最新）
        String oldDsSchema = db+"-z";// 诸城环境数据库
        DsConfig newDsConfig = new DsConfig("jdbc:mysql://localhost:3306/"+newDsSchema+"?serverTimezone=Asia/Shanghai&useSSL=false","root","root");
        DsConfig oldDsConfig = new DsConfig("jdbc:mysql://localhost:3306/"+oldDsSchema+"?serverTimezone=Asia/Shanghai&useSSL=false","root","root");
        init(db, newDsConfig, oldDsConfig);
        run();
    }


    private static void init(String db, DsConfig newDbInfo, DsConfig oldDbInfo) {
        CompareDatabase.outputDir = outputBaseDir+db+"-"+LocalDateTimeUtil.format(LocalDateTime.now(),"yyMMddHHmmss");
        FileUtil.createDir(CompareDatabase.outputDir);
        CompareDatabase.newDbInfo = newDbInfo;
        CompareDatabase.oldDbInfo = oldDbInfo;
    }

    private static void run() {
        try(Connection newConn = DriverManager.getConnection(newDbInfo.getUrl(), newDbInfo.getUsername(), newDbInfo.getPassword());
            Connection oldConn = DriverManager.getConnection(oldDbInfo.getUrl(), oldDbInfo.getUsername(), oldDbInfo.getPassword())) {

            // 表
            compareTable(newConn, oldConn);

            // 视图
            compareView(newConn, oldConn);

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 比对表
     * @param newConn
     * @param oldConn
     * @throws SQLException
     */
    private static void compareTable(Connection newConn, Connection oldConn) throws SQLException {
        Set<String> newDbAllTables = DbUtil.getTables(newConn);
        Set<String> oldDbAllTables = DbUtil.getTables(oldConn);
        for (String tableName : newDbAllTables) {

            // 忽略的表
            if (StrUtil.containsAnyIgnoreCase(tableName, ignoreTables)) {
                continue;
            }

            if (isTableNotExist(oldDbAllTables, tableName)) {
                System.out.println(tableName + "表不存在");
                String createTableSql = DbUtil.getCreateTableSql(newConn, tableName);
                generateAddTableSql(tableName,createTableSql);
            } else {
                // 对比字段
                compareColumn(newConn, oldConn, tableName);

                // 对比索引
                compareIndex(newConn, oldConn, tableName);

                // 移除表，判断是否有多余的表
                oldDbAllTables.removeIf(oldDsTable -> StrUtil.equalsIgnoreCase(oldDsTable,tableName));
            }
        }

        generateDelTableSql(oldDbAllTables);
    }

    /**
     * 比对视图
     * @param newConn
     * @param oldConn
     * @throws SQLException
     */
    private static void compareView(Connection newConn, Connection oldConn) throws SQLException {
        Set<String> viewNameSet = DbUtil.getViews(newConn);
        if (CollUtil.isNotEmpty(viewNameSet)) {
            List<String> content = new ArrayList<>();
            content.add("-- 有以下视图，请手动确认：");
            for (String viewName : viewNameSet) {
                String createViewSql = DbUtil.getCreateViewSql(newConn, viewName);
                content.add(StrUtil.appendIfMissing(createViewSql,";"));
            }
            String absoluteFilePath = getAbsoluteFilePath(viewHandleDDLFileName);
            FileUtil.write(absoluteFilePath, content);
        }
    }

    /**
     * 对比索引
     * @param newConn
     * @param oldConn
     * @param tableName
     * @throws SQLException
     */
    private static void compareIndex(Connection newConn, Connection oldConn, String tableName) throws SQLException  {
        List<IndexInfo> newDsIndexInfoList = convertIndexInfo(DbUtil.getTableIndexs(newConn, tableName));
        if (CollUtil.isEmpty(newDsIndexInfoList)) {
            return;
        }
        List<String> content = new ArrayList<>();
        content.add("-- ");
        content.add("-- ");
        content.add("-- " + tableName + "表添加以下索引，请手动确认: ");
        for (IndexInfo indexInfo : newDsIndexInfoList) {
            StringBuilder ddl = new StringBuilder();
            ddl.append("ALTER TABLE ").append(tableName).append(" ADD");
            if (indexInfo.isUniqueFlag()) {
                ddl.append(" UNIQUE");
            }
            ddl.append(" INDEX ").append(indexInfo.getIndexName())
                    .append("(").append(indexInfo.getIndexColumn()).append(")");
            if (StrUtil.isNotEmpty(indexInfo.getIndexType())) {
                ddl.append(" USING ").append(indexInfo.getIndexType());
            }
            ddl.append(";");
            content.add(ddl.toString());
        }
        String absoluteFilePath = getAbsoluteFilePath(indexHandleDDLFileName);
        FileUtil.write(absoluteFilePath, content);
    }

    /**
     * 转换索引信息
     * @param indexList
     * @return
     */
    private static List<IndexInfo> convertIndexInfo(List<IndexMeta> indexList) {
        List<IndexInfo> list = new ArrayList<>();
        Map<String, List<IndexMeta>> map = indexList.stream()
                .filter(o -> !StrUtil.equals("PRIMARY", o.getKeyName())) // 过滤掉主键索引
                .collect(Collectors.groupingBy(IndexMeta::getKeyName)); // 按照key_name分组，求复合索引
        for (Map.Entry<String, List<IndexMeta>> entry : map.entrySet()) {
            String keyName = entry.getKey();
            List<IndexMeta> metaList = entry.getValue();
            Set<String> columnSet = metaList.stream()
                    .sorted(Comparator.comparingInt(IndexMeta::getSeqInIndex))
                    .map(IndexMeta::getColumnName)
                    .collect(Collectors.toSet());

            IndexInfo indexInfo = new IndexInfo();
            indexInfo.setIndexName(keyName);
            indexInfo.setIndexColumn(StrUtil.join(",",columnSet));
            indexInfo.setIndexType(metaList.get(0).getIndexType());
            indexInfo.setUniqueFlag(metaList.get(0).getNonUnique() == 0);
            list.add(indexInfo);
        }
        return list;
    }

    /**
     * 对比列
     * @param newConn
     * @param oldConn
     * @param tableName
     * @throws SQLException
     */
    private static void compareColumn(Connection newConn, Connection oldConn, String tableName) throws SQLException {
        List<ColumnMeta> toAddColumnList = new ArrayList<>();// 要添加的列
        List<ColumnMeta> toUpdColumnList = new ArrayList<>();// 要修改的列
        List<ColumnMeta> toDelColumnList = new ArrayList<>();// 要删除的列
        List<ColumnMeta> newDsTableColumns = DbUtil.getTableColumns(newConn, tableName);
        List<ColumnMeta> oldDsTableColumns = DbUtil.getTableColumns(oldConn, tableName);
        for (ColumnMeta newDsColumn : newDsTableColumns) {
            ColumnMeta oldDsColumn = isColumnExist(oldDsTableColumns, newDsColumn);
            if (oldDsColumn == null) {
                // 字段不存在
                System.out.println(tableName + "表中" + newDsColumn.getColumnName() + "列不存在");
                toAddColumnList.add(newDsColumn);
            } else {
                // 字段被修改
                if (isColumnUpdated(newDsColumn, oldDsColumn)) {
                    System.out.println(tableName + "表中" + newDsColumn.getColumnName() + "列修改过");
                    toUpdColumnList.add(newDsColumn);
                }

                // 移除字段
                oldDsTableColumns.removeIf(o -> StrUtil.equalsIgnoreCase(o.getColumnName(),newDsColumn.getColumnName()));
            }
        }
        // 剩下的就是要移除的
        toDelColumnList.addAll(oldDsTableColumns);
        generateAddColumnSql(tableName, toAddColumnList);
        generateUpdColumnSql(tableName, toUpdColumnList);
        generateDelColumnSql(tableName, toDelColumnList);
    }


    /**
     * 生成添加列SQL
     * @param tableName
     * @param toAddColumnList
     */
    private static void generateAddColumnSql(String tableName, List<ColumnMeta> toAddColumnList) {
        if (CollUtil.isEmpty(toAddColumnList)) {
            return;
        }
        List<String> content = new ArrayList<>();
        content.add("-- ");
        content.add("-- ");
        content.add("-- " + tableName + "表添加列: ");
        for (ColumnMeta columnMeta : toAddColumnList) {
            StringBuilder ddl = new StringBuilder();
            ddl.append("ALTER TABLE ").append(tableName).append(" ADD COLUMN ");
            ddl.append(columnMeta.getColumnName()).append(" ").append(columnMeta.getColumnType());

            if (StrUtil.isNotEmpty(columnMeta.getCharacterSetName())) {
                ddl.append(" CHARACTER SET ").append(columnMeta.getCharacterSetName());
            }

            if (StrUtil.isNotEmpty(columnMeta.getCollationName())) {
                ddl.append(" COLLATE ").append(columnMeta.getCollationName());
            }

            if ("NO".equalsIgnoreCase(columnMeta.getIsNullable())) {
                ddl.append(" NOT NULL");
            } else {
                ddl.append(" NULL");
            }

            if (columnMeta.getColumnDefault() != null) {
                ddl.append(" DEFAULT '").append(columnMeta.getColumnDefault()).append("'");
            }

            if (columnMeta.getColumnComment() != null) {
                ddl.append(" COMMENT '").append(columnMeta.getColumnComment()).append("'");
            }
            ddl.append(";");
            content.add(ddl.toString());
        }
        String absoluteFilePath = getAbsoluteFilePath(columnHandleDDLFileName);
        FileUtil.write(absoluteFilePath, content);
    }

    /**
     * 生成修改列SQL
     * @param tableName
     * @param toUpdColumnList
     */
    private static void generateUpdColumnSql(String tableName, List<ColumnMeta> toUpdColumnList) {
        if (CollUtil.isEmpty(toUpdColumnList)) {
            return;
        }
        List<String> content = new ArrayList<>();
        content.add("-- ");
        content.add("-- ");
        content.add("-- " + tableName + "表修改列: ");
        for (ColumnMeta columnMeta : toUpdColumnList) {
            StringBuilder ddl = new StringBuilder();
            ddl.append("ALTER TABLE ").append(tableName).append("  MODIFY COLUMN ");
            ddl.append(columnMeta.getColumnName()).append(" ").append(columnMeta.getColumnType());

            if (StrUtil.isNotEmpty(columnMeta.getCharacterSetName())) {
                ddl.append(" CHARACTER SET ").append(columnMeta.getCharacterSetName());
            }

            if (StrUtil.isNotEmpty(columnMeta.getCollationName())) {
                ddl.append(" COLLATE ").append(columnMeta.getCollationName());
            }

            if ("NO".equalsIgnoreCase(columnMeta.getIsNullable())) {
                ddl.append(" NOT NULL");
            } else {
                ddl.append(" NULL");
            }

            if (columnMeta.getColumnDefault() != null) {
                ddl.append(" DEFAULT '").append(columnMeta.getColumnDefault()).append("'");
            }

            if (columnMeta.getColumnComment() != null) {
                ddl.append(" COMMENT '").append(columnMeta.getColumnComment()).append("'");
            }
            ddl.append(";");
            content.add(ddl.toString());
        }
        String absoluteFilePath = getAbsoluteFilePath(columnHandleDDLFileName);
        FileUtil.write(absoluteFilePath, content);
    }

    /**
     * 生成删除列SQL
     * @param tableName
     * @param toDelColumnList
     */
    private static void generateDelColumnSql(String tableName, List<ColumnMeta> toDelColumnList) {
        if (CollUtil.isEmpty(toDelColumnList)) {
            return;
        }
        List<String> content = new ArrayList<>();
        content.add("-- ");
        content.add("-- ");
        content.add("-- " + tableName + "表删除列: ");
        for (ColumnMeta columnMeta : toDelColumnList) {
            StringBuilder ddl = new StringBuilder();
            ddl.append("-- ALTER TABLE ").append(tableName).append(" DROP COLUMN ");
            ddl.append(columnMeta.getColumnName()).append(";");
            content.add(ddl.toString());
        }
        String absoluteFilePath = getAbsoluteFilePath(columnHandleDDLFileName);
        FileUtil.write(absoluteFilePath, content);
    }

    /**
     * 生成创建表SQL
     * @param tableName
     * @param createTableSql
     */
    private static void generateAddTableSql(String tableName, String createTableSql) {
        createTableSql = StrUtil.appendIfMissing(createTableSql, ";");
        List<String> content = new ArrayList<>();
        content.add("-- ");
        content.add("-- ");
        content.add("-- 创建表：" + tableName);
        content.add(createTableSql);
        String absoluteFilePath = getAbsoluteFilePath(tableHandleDDLFileName);
        FileUtil.write(absoluteFilePath, content);
    }

    /**
     * 生成删除表SQL
     * @param toDelTableNames
     */
    private static void generateDelTableSql(Set<String> toDelTableNames) {
        if (CollUtil.isEmpty(toDelTableNames)) {
            return;
        }
        List<String> content = new ArrayList<>();
        content.add("-- ");
        content.add("-- ");
        content.add("-- 需要删除的表: ");
        for (String tableName : toDelTableNames) {
            content.add("-- DROP TABLE IF EXISTS " + tableName + ";");
        }
        String absoluteFilePath = getAbsoluteFilePath(tableHandleDDLFileName);
        FileUtil.write(absoluteFilePath, content);
    }

    /**
     * 判断列是否被更改过
     * @param newDsColumn
     * @param oldDsColumn
     * @return
     */
    private static boolean isColumnUpdated(ColumnMeta newDsColumn, ColumnMeta oldDsColumn) {
        // 列名
        if (!StrUtil.equalsIgnoreCase(newDsColumn.getColumnName(),oldDsColumn.getColumnName())){
            return true;
        }

        // 字段类型
        if (!StrUtil.equals(newDsColumn.getColumnType(),oldDsColumn.getColumnType())){
            return true;
        }

        // 是否可空
        if (!StrUtil.equals(newDsColumn.getIsNullable(),oldDsColumn.getIsNullable())) {
            return true;
        }

        // 默认值
        if (!StrUtil.equals(newDsColumn.getColumnDefault(),oldDsColumn.getColumnDefault())) {
            return true;
        }

        // 注释
        if (!StrUtil.equals(newDsColumn.getColumnComment(),oldDsColumn.getColumnComment())) {
            return true;
        }

        // 字符集
        if (!StrUtil.equals(newDsColumn.getCharacterSetName(),oldDsColumn.getCharacterSetName())) {
            return true;
        }

        // 排序规则
        if (!StrUtil.equals(newDsColumn.getCollationName(),oldDsColumn.getCollationName())) {
            return true;
        }

        return false;
    }

    /**
     * 判断列是否存在（列名大小写不敏感）
     * @param oldDsTableColumns
     * @param newDsColumn
     * @return
     */
    private static ColumnMeta isColumnExist(List<ColumnMeta> oldDsTableColumns, ColumnMeta newDsColumn) {
        for (ColumnMeta oldDsColumn : oldDsTableColumns) {
            if (StrUtil.equalsIgnoreCase(oldDsColumn.getColumnName(), newDsColumn.getColumnName())) {
                return oldDsColumn;
            }
        }
        return null;
    }


    /**
     * 判断表是否不存在（按照表名大小写不敏感来判断）
     * @param oldDbAllTable
     * @param tableName
     * @return
     */
    private static boolean isTableNotExist(Set<String> oldDbAllTable, String tableName) {
        for (String oldDsTableName : oldDbAllTable) {
            if (StrUtil.equalsIgnoreCase(oldDsTableName, tableName)) {
                return false;
            }
        }
        return true;
    }

    /**
     * 获取绝对文件路径
     * @param tableHandleDDLFileName
     * @return
     */
    private static String getAbsoluteFilePath(String tableHandleDDLFileName) {
        String base = StrUtil.appendIfMissing(outputDir, File.separator);
        return StrUtil.appendIfMissing(base, tableHandleDDLFileName);
    }

}
